package io.renren.modules.basedata.service.impl;

import cn.com.taiji.common.manager.net.http.binclient.ApiRequestException;
import cn.com.taiji.sdk.comm.ETCCommHelper;
import cn.com.taiji.sdk.model.comm.protocol.sdts.backlist.CardBlackListAllDownloadRequest;
import cn.com.taiji.sdk.model.comm.protocol.sdts.backlist.CardBlackListIncrDownloadRequest;
import io.renren.common.utils.*;
import io.renren.modules.basedata.dao.CardBlackDownloadDao;
import io.renren.modules.basedata.dao.OperlogDao;
import io.renren.modules.basedata.entity.*;
import io.renren.modules.basedata.service.CardBlackListDownloadService;
import lombok.extern.slf4j.Slf4j;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.Date;
import java.io.File;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

@Service
@Slf4j
public class CardBlackListDownloadServiceImpl implements CardBlackListDownloadService {

    @Autowired
    CardBlackDownloadDao cardBlackDownloadDao;
    @Autowired
    OperlogDao operlogDao;

    private String secondParentfileName;//二级压缩包文件名
    private String secondParentpath; //二级压缩包解压后的文件夹名
    private File file = null;
    private String filePath;
    private String fileId;
    /**
     * @author WANGYP
     * @version 实现用户卡黑名单全量下载
     */
    @SuppressWarnings("unused")
    public String cardBlackListDownload() throws Exception {

        tbl_Operlog operlog = new tbl_Operlog();

        Date datetime = new Date();//获取当前时间

        Integer infoCount = 0;

        tbl_VersonInfo versionInfo = null;

        String version = DateUtil.formatDateTime1(datetime).substring(0, 8);//YYYYMMDD生成要请求的全量黑名单版本号

        HashMap<String, Object> map = new HashMap<String, Object>();

        TBL_CardStListPara cardBlackhis ;//2018-03-06

        Integer downinfoVersion = getDownInfoVersion();

        List<Tbl_CardBlackListDown> list =null;

        map.put("version", version);
        map.put("fileType",1);//用户卡黑名单
        map.put("type", 0);//全量
        map.put("result", 1);//已处理

        /**判断是否存在当天已处理的全量版本记录信息***/
        infoCount = cardBlackDownloadDao.getVersionInfo(map);
       // infoCount =  (Integer) sqlMapClient.queryForObject("download_ORACLE.getVersionInfo", map);

        if(infoCount>0){

            log.info("当天部中心全量用户卡黑名单已下载成功，版本号："+version);

        }else{//如果没有当天的全量用户卡黑名单记录，则需要发送请求进行下载

            int count=0;//记录全量用户卡黑名单数量

            int operResult=0;

            String downloadPath = "D:\\";
            //生成全量黑名单申请版本号
            log.info("用户卡黑名单全量版本："+version);
            //发送请求
            CardBlackListAllDownloadRequest request = new CardBlackListAllDownloadRequest();

            request.setVersion(version);
            //生成请求文件名
            String fileName = ETCCommHelper.getFileName(request,"65");

            String getFileName ;

            try{
                //接收部里下发的黑名单文件
                File file =  ETCCommHelper.download(fileName,request,"D:\\");
//			File file = new File("F:\\BASIC_CARDBLACKLISTDOWN_RES_65_20171023165050007.zip");//测试方法

                getFileName = file.getName();//获取下载文件名

                if(file!=null){//下载成功

                    log.info("部中心用户卡黑名单全量下载成功，下载文件为："+file.getAbsolutePath());

                    //清空黑名单表
                    cardBlackDownloadDao.deleteCardBlackAllListDownInfo();
                   // sqlMapClient.delete("download_ORACLE.deleteCardBlackAllListDownInfo");
                    //在下载路径下解压原始压缩包
                    ZIPUtil.unzip(file.getAbsolutePath(),downloadPath , true);
                    //获取原始压缩包下的二级压缩包文件
                    String filePath = downloadPath+file.getName().substring(0, file.getName().lastIndexOf("."));
                    List<File> unzipfiles = ZIPUtil.getFiles(filePath);
                    //获取原始压缩包下的二级压缩包(二级压缩包只有一个)
                    if(unzipfiles!=null&&unzipfiles.size()>0){

                        File zipfile =  unzipfiles.get(0);//F:\BASIC_CARDBLACKLISTDOWN_RES_65_20171023165050007\20171023.zip

                        String workFilePath = "D:\\unzip\\work";//保存二级压缩包文件至该目录下

                        String successFilePath ="D:\\unzip\\success";//将处理成功的原始压缩包保存至该目录下

                        String errorFilePath ="D:\\unzip\\error";//将处理失败的原始压缩包保存至该目录下

                        String secondParentfileName = zipfile.getName();//二级压缩包文件名带后缀.zip

                        String secondParentpath = secondParentfileName.substring(0, secondParentfileName.lastIndexOf("."));//二级压缩包解压后的文件夹名
                        //将二级压缩包转移至workFilePath下
                        ZIPUtil.moveFile(zipfile.getAbsolutePath(),workFilePath+"\\"+secondParentfileName );//F:\\unzip\\work\\20171023.zip
                        //将二级压缩包在workFilePath解压缩
                        ZIPUtil.unzip(workFilePath+"\\"+secondParentfileName, workFilePath, true);//F:\\unzip\\work\\20171023
                        //获取二级压缩包解压后的各省份压缩包文件(三级压缩包)
                        List<File> secondfiles = ZIPUtil.getFiles(workFilePath+"\\"+secondParentpath);//F:\\unzip\\work\\20171023
                        //逐个对各省份的压缩包进行解压，并对解压后的JSON文件进行解析
                        boolean flag = false;

                        for(int i=0;i<secondfiles.size();i++){
                            //获取某个三级压缩包的文件名
                            String thirdParentfileName = secondfiles.get(i).getName();

                            String thirdParentpath = thirdParentfileName.substring(0, thirdParentfileName.lastIndexOf("."));//

                            System.out.println(secondfiles.get(i).getAbsolutePath());//F:\\unzip\\work\\20171023\\20171023_12.zip
                            //解压三级压缩包
                            ZIPUtil.unzip(workFilePath+"\\"+secondParentpath+"\\"+thirdParentfileName, workFilePath+"\\"+secondParentpath, true);
                            //获取三级压缩包解压后的JSON文件
                            List<File>	thirdfiles = ZIPUtil.getFiles(workFilePath+"\\"+secondParentpath+"\\"+thirdParentpath);//F:\\unzip\\work\\20171023\\20171023\\20171023_12
                            //循环处理每个JSON文件
                            for(int m=0;m<thirdfiles.size();m++){
                                //获取JSON文件中的字符串
                                String jsonStr = ZIPUtil.getjsonStr(thirdfiles.get(m).getAbsolutePath());
                                //字符串转化为JSON数组对象
                                JSONArray jsonArray = JSONArray.fromObject(jsonStr);
                                //循环处理每个JSON对象
                                Date a=new Date();
                                log.info("开始处理一个Json文件，当前时间："+a);
                                if(jsonArray!=null&&jsonArray.size()>0){

                                    Tbl_CardBlackListDown CardBlackListDownInfo = new Tbl_CardBlackListDown();
                                    list =  new ArrayList<Tbl_CardBlackListDown>();
                                    //将json数据转化为list
                                    for(int  n = 0; n <jsonArray.size(); n++){

                                        JSONObject jsonObject = jsonArray.getJSONObject(n);
                                        CardBlackListDownInfo = getCardBlackListDownInfo(jsonObject);
                                        CardBlackListDownInfo.setVersion(version);
                                        list.add(CardBlackListDownInfo);
                                    }


                                    try{

                                        //sqlMapClient.startTransaction();


                                        //sqlMapClient.startBatch();


                                        for(int  n = 0; n <list.size(); n++){

                                            //插入数据库
                                            cardBlackDownloadDao.insertCardBlackAllListDownInfo(list.get(n));
                                            //sqlMapClient.insert("download_ORACLE.insertCardBlackAllListDownInfo",list.get(n));

                                            count++;

                                            flag=true;
                                        }

                                        //sqlMapClient.executeBatch();
                                        //sqlMapClient.commitTransaction();
                                    }catch(Exception e){
                                        log.info("用户卡全量黑名单插入数据库异常:"+e.getMessage());
                                        flag = false;
                                        //sqlMapClient.getCurrentConnection().rollback();
                                    }finally{
                                        //sqlMapClient.endTransaction();
                                    }

                                }
                                Date b = new Date();
                                log.info("处理一个Json文件完成，耗时："+(a.getTime()-b.getTime())/1000+"秒");
                            }

                        }

                        //删除workFilePath下的二级压缩包和解压缩文件
                        ZIPUtil.delFile(workFilePath+"\\"+secondParentfileName);//二级压缩包

                        ZIPUtil.deleteDir(new File(workFilePath+"\\"+secondParentpath));//删除解压缩文件及文件下的所有文件

                        ZIPUtil.deleteDir(new File(filePath));//删除解压缩文件及文件下的所有文件

                        if(flag){//如果处理成功
                            /****将原始压缩包转移至successFilePath下***/
                            ZIPUtil.moveFile(file.getAbsolutePath(), successFilePath+"\\"+file.getName());

                            operResult =1;

                            /**处理成功，向版本记录表中写入信息**/

                            versionInfo = getVersionInfo(1,0,version,count,getFileName);
                            cardBlackDownloadDao.insertVersionInfo(versionInfo);
                           // sqlMapClient.insert("download_ORACLE.insertVersionInfo",versionInfo);

                        }else{
                            //将原始压缩包转移至errorFilePath下
                            ZIPUtil.moveFile(file.getAbsolutePath(), errorFilePath+"\\"+file.getName());

                            operResult =0;

                        }

                        operlog = getOperlog(15,2,fileName,file.getName(),operResult, "处理数据条数："+count,"下载用户卡全量黑名单文件完成");
                        operlogDao.insertlog(operlog);
                        //sqlMapClient.insert("operlog_ORACLE.insertlog",operlog);

                    }


                }else{//下载失败

                    log.info("下载失败");

                    operlog = getOperlog(15,2,fileName,file.getName(),operResult, "处理数据条数："+count,"下载用户卡全量黑名单文件失败");
                    operlogDao.insertlog(operlog);
                   // sqlMapClient.insert("operlog_ORACLE.insertlog",operlog);

                }

            }catch (IOException e) {
                //TODO 网络异常处理,各省补全
                log.info("下载用户卡黑名单全量"+version+"版本IO异常:"+e.getMessage());
                e.printStackTrace();
            }catch (ApiRequestException apie) {
                //TODO 系统自定义异常处理，各省补全
                log.info("下载用户卡黑名单全量"+version+"版本Apie异常:"+apie.getMessage());
                apie.printStackTrace();
                System.out.println("错误码："+apie.getErrCode()+" 错误信息："+apie.getMessage());
            }
        }
        return "用户卡黑名单全量下载结束...";

    }


    public Tbl_CardBlackListDown getCardBlackListDownInfo(JSONObject jsonObject){
        Tbl_CardBlackListDown CardBlackListDownInfo = new Tbl_CardBlackListDown();
        CardBlackListDownInfo.setInsertTime(DateUtil.formatDateTime6(jsonObject.getString("insertTime")));
        CardBlackListDownInfo.setIssuerId(jsonObject.getString("issuerId"));
        CardBlackListDownInfo.setCreationTime(DateUtil.formatDateTime6(jsonObject.getString("creationTime")));
        CardBlackListDownInfo.setType(jsonObject.getInt("type"));
        CardBlackListDownInfo.setCardId(jsonObject.getString("cardId"));
        CardBlackListDownInfo.setStatus(jsonObject.getInt("status"));
        return CardBlackListDownInfo;

    }

    /**获取当前省内下发版本号******/
    public Integer getDownInfoVersion() throws SQLException{
        Integer cardinfo = cardBlackDownloadDao.getCardInfo();
        //Integer cardinfo  = (Integer) sqlMapClient2.queryForObject("download_ORACLE.getCardInfo");

        return cardinfo;
    }

    /**封装历史黑名单数据******/

    public TBL_CardStListPara getCardBlackHis(Tbl_CardBlackListDown CardBlackListDownInfo,int listType){

        TBL_CardStListPara cardinfo = new TBL_CardStListPara();

        cardinfo.setCardId(CardBlackListDownInfo.getCardId().substring(4, 20));//16位卡号
//		cardinfo.setCardType(cardType);//卡类型,暂不启用
        cardinfo.setIssuerId(CardBlackListDownInfo.getIssuerId());//发行方ID
        cardinfo.setLicense("0");//车牌，默认为0
        cardinfo.setNetNo(Integer.valueOf(CardBlackListDownInfo.getIssuerId().substring(0,4)));//发行方区域编码
//		cardinfo.setSpare1(spare1);//
//		cardinfo.setSpare2(spare2);//下发版本号
        cardinfo.setStartTime(CardBlackListDownInfo.getCreationTime());//生效时间
        if(CardBlackListDownInfo.getStatus()==2){//黑名单状态
            cardinfo.setStatus(0);
        }else{
            cardinfo.setStatus(CardBlackListDownInfo.getType());
        }
        cardinfo.setVersion(CardBlackListDownInfo.getVersion());//部中心版本号
        cardinfo.setUpdateTime(CardBlackListDownInfo.getReceiveTime());//更新时间
        cardinfo.setListType(listType);//黑名单类型0-全量1-增量
        return cardinfo;
    }


    /**封装版本记录信息
     * @throws SQLException **/
    public tbl_VersonInfo getVersionInfo(int fileType,int type,String version,int count,String getFileNme) throws SQLException{

        tbl_VersonInfo versionInfo = new tbl_VersonInfo();
        String recordId = DateUtil.formatDateTime1(new Date())+fileType+type;
        versionInfo.setRecordId(recordId);
        versionInfo.setFileType(fileType);
        versionInfo.setType(type);
        versionInfo.setVersion(version);
        versionInfo.setDownCount(count);
        versionInfo.setCreatTime(new Date());
        versionInfo.setResult(1);
        versionInfo.setDealTime(new Date());
        versionInfo.setPackageName(getFileNme);
        versionInfo.setSpare1(0);
        int downverson = getDownInfoVersion();
        versionInfo.setSpare2(downverson);

        return versionInfo;
    }


    //获取operlog信息
    public tbl_Operlog getOperlog(int operType,int softType,String sendFileName,String reFileName,int operResult,String reResult, String content){
        tbl_Operlog operlog = new tbl_Operlog();
        operlog.setWastesn(new GUID().toString());
        operlog.setOperTime(new Date());
        operlog.setOperType(operType);
        operlog.setSoftType(softType);
        operlog.setSendFileName(sendFileName);
        operlog.setReFileName(reFileName);
        operlog.setOperResult(operResult);
        operlog.setReResult(reResult);
        operlog.setContent(content);
        return operlog;
    }



    /**
     * @author WANGYP
     * @version 实现用户卡黑名单增量下载
     */
    @SuppressWarnings({ "unused", "unchecked"})
    public String cardBlackListIncreDownload() throws Exception {

        tbl_Operlog operlog = new tbl_Operlog();

        Date datetime = new Date();

        List<tbl_VersonInfo> versionInfoList = null;

        String full_version = DateUtil.formatDateTime1(datetime).substring(0, 8);//全量用户卡黑名单版本

        String inc_version = DateUtil.formatDateTime1(datetime).substring(0, 8)+"0000";//默认增量黑名单版本为当天0时0分

        int full_infoCount = 0;//全量记录数据总数

        int inc_infoCount = 0;//增量记录数据总数

        int operResult=0;//操作结果0-失败1-成功

        tbl_VersonInfo versionInfo = null;

        TBL_CardStListPara cardBlackhis ;//2018-03-06

        Integer downinfoVersion = getDownInfoVersion();

        String downloadPath = "D:\\";

        HashMap<String, Object> full_versionMap = new HashMap<String, Object>();

        full_versionMap.put("version", full_version);
        full_versionMap.put("fileType",1);//用户卡黑名单
        full_versionMap.put("type", 0);//全量
        full_versionMap.put("result", 1);//已处理

        /**判断是否存在当天已处理的全量版本记录信息***/
        full_infoCount = cardBlackDownloadDao.getVersionInfo(full_versionMap);
        //full_infoCount =  (Integer) sqlMapClient.queryForObject("download_ORACLE.getVersionInfo", full_versionMap);
        /***如果不存在已经处理成功的当天的全量黑名单下发记录，则不向部中心请求增量黑名单数据***/
        if(full_infoCount==0){

            log.info("今天的全量用户卡黑名单还未下载，暂不向部中心发送增量用户卡黑名单请求");

        }else{

            /**1.如果存在已经处理成功的当天的全量黑名单下发记录，则判断是否有当天的已处理的增量黑名单记录；
             * 2.如果没有，则向部中心请求当天零时零分的增量黑名单；
             * 3.如果存在当天的已处理的增量黑名单记录，则根据最新已处理的黑名单版本请求15分钟后的增量黑名单版本*/
            HashMap<String, Object> inc_versionMap = new HashMap<String, Object>();

            inc_versionMap.put("version", full_version);
            inc_versionMap.put("fileType",1);//用户卡黑名单
            inc_versionMap.put("type", 1);//增量
            inc_versionMap.put("result", 1);//已处理
            versionInfoList = cardBlackDownloadDao.getVersionInfoList(inc_versionMap);
            //versionInfoList =  sqlMapClient.queryForList("download_ORACLE.getVersionInfoList", inc_versionMap);//获取增量版本信息

            if(versionInfoList!=null&&versionInfoList.size()>0){

                inc_version = getInc_version(versionInfoList.get(0).getVersion());

                if(inc_version==null){

                    log.info("增量版本不存在,程序退出等待下次执行...");

                    return "增量版本不存在,程序退出等待下次执行...";

                }

            }

            log.info("卡黑名单增量版本号："+inc_version);

            CardBlackListIncrDownloadRequest request = new CardBlackListIncrDownloadRequest();

            request.setVersion(inc_version);

            String fileName = ETCCommHelper.getFileName(request,"65");

            String getFileName;

            try{

                File file =  ETCCommHelper.download(fileName,request,"D:\\");

                getFileName = file.getName();

//			File file = new File("D:\\BASIC_CARDBLACKLISTINCREDOWN_RES_65_20171222125426416.zip");

                System.out.println("卡黑名单增量下载成功，下载文件为："+file.getAbsolutePath());
                log.info("卡黑名单增量下载成功，下载文件为："+file.getAbsolutePath());

                //先在下载路径下解压原始压缩包
                ZIPUtil.unzip(file.getAbsolutePath(),downloadPath , true);
                //获取原始压缩包下的二级压缩包文件
                String filePath = downloadPath+file.getName().substring(0, file.getName().lastIndexOf("."));

                List<File> unzipfiles = ZIPUtil.getFiles(filePath);//201709150030.zip

                String workFilePath = "D:\\unzip\\workCard";//保存二级压缩包文件至该目录下

                String successFilePath ="D:\\unzip\\success";//将处理成功的原始压缩包保存至该目录下

                String errorFilePath ="D:\\unzip\\error";//将处理失败的原始压缩包保存至该目录下
                //获取原始压缩包下的二级压缩包(二级压缩包是多个版本的增量名单)201709150030.zip
                if(unzipfiles!=null&&unzipfiles.size()>0){

                    for(int i=0;i<unzipfiles.size();i++){

                        File zipfile =  unzipfiles.get(i);//F:\BASIC_CARDBLACKLISTDOWN_RES_65_20171023165050007\201709150030.zip

                        secondParentfileName = zipfile.getName();//二级压缩包文件名带后缀.zip

                        secondParentpath = secondParentfileName.substring(0, secondParentfileName.lastIndexOf("."));//二级压缩包解压后的文件夹名
                        //将二级压缩包转移至workFilePath下
                        ZIPUtil.moveFile(zipfile.getAbsolutePath(),workFilePath+"\\"+secondParentfileName );//F:\\unzip\\work\\201709150030.zip
                        //将二级压缩包在workFilePath解压缩
                        ZIPUtil.unzip(workFilePath+"\\"+secondParentfileName, workFilePath, true);//F:\\unzip\\work\\201709150030
                        //获取二级压缩包解压后的各省份压缩包文件(三级压缩包)
                        List<File> secondfiles = ZIPUtil.getFiles(workFilePath+"\\"+secondParentpath);//F:\\unzip\\work\\201709150030

                        for(int m=0;m<secondfiles.size();m++){
                            String secondfileName = secondfiles.get(m).getName();//F:\\unzip\\work\\201709150030\\201709150030_data.zip
                            String currtentPath = workFilePath+"\\"+secondParentpath;
                            Validate validate = new Validate();
                            ArrayList<String> validatelist = new ArrayList<String>();
                            ArrayList<String> cardBlacklist = new ArrayList<String>();
                            List<Tbl_CardBlackListDown> allcardBlackList = new ArrayList<Tbl_CardBlackListDown>();
                            if(secondfileName.indexOf(".json")!=-1){
                                //解析校验文件至内存
//							validate =ZIPUtil.analysisValidateJson(secondfiles.get(m));
                                //删除校验文件
//							secondfiles.get(m).delete();
//							validatelist = new ArrayList<String>();
//							for(Abstract abstrac:validate.getAbstract()){
//								validatelist.add(abstrac.getId());
//							}
                            }else{
                                //解压数据包
                                ZIPUtil.unzip(currtentPath+"\\"+secondfiles.get(m).getName(), currtentPath, true);//F:\\unzip\\work\\201709150030\\201709150030_data
                                //删除数据包
                                ZIPUtil.delFile(currtentPath+"\\"+secondfiles.get(m).getName());
                                //获取该版本的各省份压缩包
                                List<File> zfileListfxf =ZIPUtil.getFiles(currtentPath+"\\"+secondfileName.substring(0, secondfileName.lastIndexOf(".")));//获取F:\\unzip\\work\\201709150030\\201709150030下的压缩包

                                for (int k = 0; k < zfileListfxf.size(); k++) {

                                    List<Tbl_CardBlackListDown> cardBlackList= ZIPUtil.analysisJson(zfileListfxf.get(k),Tbl_CardBlackListDown.class);
                                    //逐条写入数据库
                                    for(int n=0;n<cardBlackList.size();n++){

                                        cardBlackList.get(n).setVersion(inc_version);//2018-01-29 added by WANGYP

                                        try{
                                            if(cardBlackList.get(n).getStatus()==1){//如果是加入黑名单，则执行插入语句
                                                cardBlackDownloadDao.insertCardBlackIncreListDownInfo(cardBlackList.get(n));
                                               // sqlMapClient.insert("download_ORACLE.insertCardBlackIncreListDownInfo",cardBlackList.get(n));
                                            }else{//如果是解除黑名单，则执行删除语句
                                                cardBlackDownloadDao.deleteCardBlackIncreListDownInfo(cardBlackList.get(n));
                                                //sqlMapClient.delete("download_ORACLE.deleteCardBlackIncreListDownInfo",cardBlackList.get(n));
                                            }
                                            inc_infoCount++;

                                        }catch(Exception e){

                                            inc_infoCount++;

                                            continue;

                                        }

                                        /*****向历史表中插入数据*******/
                                        try{
                                            /********生成历史数据并插入历史表
                                             * 1.封装生成历史数据
                                             * 2.插入历史数据
                                             * *********/

                                            cardBlackhis  = getCardBlackHis(cardBlackList.get(n),1);//0-表示全量名单1-增量名单

                                            cardBlackhis.setSpare2(downinfoVersion.toString());
                                            cardBlackDownloadDao.insertHisCardBlackDownInfo(cardBlackhis);
                                            //sqlMapClient2.insert("download_ORACLE.insertHisCardBlackDownInfo",cardBlackhis);
                                            /*****************/

                                        }catch(Exception e){

                                            log.info("插入历史表失败："+cardBlackList.get(n).getCardId());

                                            continue;

                                        }

                                        /*****************************/

                                    }

                                    System.out.println(cardBlackList.size());

                                }
                                //每处理完一个版本的文件需要进行校验

//							allcardBlackList = sqlMapClient.queryForList("download_ORACLE.allCardBlackListDownInfo");
//
//							cardBlacklist = getCardBlacklist(allcardBlackList);
//
//							if(cardBlacklist==validatelist){
//
//								System.out.println("校验成功!");
//
//							}else{
//
//								System.out.println("校验失败!");
//
//							}


                            }


                        }


                    }
                    //将原始压缩包转移至successFilePath目录下
                    ZIPUtil.moveFile(file.getAbsolutePath(),successFilePath+"\\"+file.getName());
                    //将workFilePath下的文件全部删掉
                    ZIPUtil.deleteDir(new File(workFilePath+"\\"+secondParentfileName));
                    ZIPUtil.deleteDir(new File(workFilePath+"\\"+secondParentpath));
                    //将下载目录下的解压缩文件全部删掉
                    ZIPUtil.delFile(filePath);

                    /**处理成功，向版本记录表中写入信息**/

                    versionInfo = getVersionInfo(1,1,inc_version,inc_infoCount,getFileName);
                    cardBlackDownloadDao.insertVersionInfo(versionInfo);
                    //sqlMapClient.insert("download_ORACLE.insertVersionInfo",versionInfo);


                }else{//下载失败
                    System.out.println("下载失败");
                }

            }catch (IOException e) {
                //TODO 网络异常处理,各省补全
                log.info("下载用户卡黑名单增量"+inc_version+"版本IO异常:"+e.getMessage());
                e.printStackTrace();
            }catch (ApiRequestException apie) {
                //TODO 系统自定义异常处理，各省补全
                log.info("下载用户卡黑名单增量"+inc_version+"版本Apie异常:"+apie.getMessage());
                apie.printStackTrace();
                System.out.println("错误码："+apie.getErrCode()+" 错误信息："+apie.getMessage());
                if(apie.getErrCode()==711){//针对711异常，部中心无对应增量版本数据进行处理

                    versionInfo = getVersionInfo(1,1,inc_version,inc_infoCount,"无数据");
                    cardBlackDownloadDao.insertVersionInfo(versionInfo);
                    //sqlMapClient.insert("download_ORACLE.insertVersionInfo",versionInfo);
                }
            }
        }
        return "用户卡黑名单增量下载结束...";
    }


    /**
     * 获取将要请求的黑名单版本号
     *
     * **/
    public String getInc_version(String inc_version){

        String min = inc_version.substring(10,12);

        String hour = inc_version.substring(8,10);

        String date = inc_version.substring(0,8);

        if(min.equals("00")){

            min = "15";

        }else if(min.equals("15")){

            min = "30";

        }else if(min.equals("30")){

            min = "45";

        }else{

            min ="00";

            hour = OperType.compStr(String.valueOf(Integer.valueOf(hour)+1),2);

            if(hour.equals("24")){//如果已经是当天最后一个版本，则返回null,不再进行处理

                return null;
            }
        }

        inc_version = date+hour+min;

        return inc_version;
    }



    /**
     *从全量黑名单中每10000条获取一条数据;
     *
     * */
    public ArrayList<String> getCardBlacklist(List<Tbl_CardBlackListDown> allcardBlackList){
        ArrayList<String> cardBlacklist = new ArrayList<String>();

        int m = allcardBlackList.size()/10000;//取整

        int n = allcardBlackList.size()%10000;//取余

        int num = 0;

        if(n==0){

            for(int k=0;k<m;k++){

                num = k*10000+1;

                cardBlacklist.add(allcardBlackList.get(num).getCardId());

            }


        }else{

            for(int j=0;j<m+1;j++){

                num = j*10000+1;

                cardBlacklist.add(allcardBlackList.get(num).getCardId());

            }

        }

        return cardBlacklist;
    }

    /**
     * @author WANGYP
     * @version 实现将用户卡黑名单从部中心下发表下发至省中心下发表
     */
    @SuppressWarnings("unchecked")
    public String cardBlackListDownloadCenterbak() throws Exception {

        log.info("开始查询营改增全量用户卡黑名单数据");

        List<Tbl_cardBlackList> BlackList = new ArrayList<Tbl_cardBlackList>();

        int cardType=0;//0-用户卡1-OBU
        int cardNum = cardBlackDownloadDao.allCardBlackListCount();
        //int cardNum = (Integer) sqlMapClient.queryForObject("user_ORACLE.allCardBlackListCount");

        TBL_CardStListPara cardSt = null;//中心下发黑名单表

        boolean flag =true;

        int versionInfo = 0;//记录是否存在全量版本信息

        /**查询是否存在当天已处理的全量版本记录信息***/
        versionInfo = getFullVersionInfo();

        /**根据下发记录获取黑名单数据**/

        if(cardNum>0&&versionInfo>0){//如果数据不为空，并且当天的全量黑名单已下载，则清空中心库数据

            try{

               // sqlMapClient2.startTransaction();

               // sqlMapClient2.startBatch();
                cardBlackDownloadDao.deleteBlackList(cardType);
                //sqlMapClient2.delete("user_ORACLE.deleteBlackList",cardType);//先清空卡数据

               // sqlMapClient2.executeBatch();

               // sqlMapClient2.commitTransaction();

            }catch(Exception e){
                log.info("删除卡数据失败："+e.getMessage());
               // sqlMapClient2.getCurrentConnection().rollback();
                flag = false;
            }finally{
               // sqlMapClient2.endTransaction();
            }

            int onerun = 50000;

            int runnum =0;

            runnum = cardNum%onerun==0?(cardNum/onerun):(cardNum/onerun)+1;

            Date nowTime = new Date();

            try{
               // sqlMapClient2.startTransaction();

                for(int r =1;r<=runnum;r++){
                    log.info(" 开始查询第"+(r)+"批用户卡数据");
                    HashMap<String,Object> paraMap = new HashMap<String,Object>();
                    paraMap.put("limit", onerun);
                    paraMap.put("start", (r - 1) * onerun);
                    paraMap.put("receiveTime", nowTime);
                    log.info("从"+(r - 1) * onerun+"到"+((r - 1) * onerun+onerun));
                    BlackList = null;
                    BlackList = cardBlackDownloadDao.getBlackList(paraMap);
                    //BlackList =sqlMapClient.queryForList("user_ORACLE.getBlackList", paraMap);//根据版本号和表名获取黑名单数据
                    log.info("本次共有数据："+BlackList.size());

                  //  sqlMapClient2.startBatch();
                    for(int i=BlackList.size()-1;i>-1;i--){

                        cardSt = genCardStPara(BlackList.get(i));//黑名单数据转化

                        cardBlackDownloadDao.insertBlackList(cardSt);
                       // sqlMapClient2.insert("user_ORACLE.insertBlackList", cardSt);

                    }
                  //  sqlMapClient2.executeBatch();

                    log.info("第"+(r)+"批用户卡数据处理完成");

                }
              //  sqlMapClient2.commitTransaction();
            }catch(Exception e){
                log.info("批处理用户卡数据发生异常："+e.getMessage());
               // sqlMapClient2.getCurrentConnection().rollback();
                flag = false;
            }finally{
               // sqlMapClient2.endTransaction();
            }


            //更新下发记录
            if(flag){
                cardBlackDownloadDao.updateConfig();
                //sqlMapClient2.update("check_ORACLE.updateConfig");

                log.info("用户卡更新下发记录时间:"+DateUtil.formatDateTime(new Date()));

            }




        }else{

            log.info("当前无新下发的用户卡黑名单记录");

        }


        return "用户卡黑名单全量下载至省中心结束...";
    }

    /**
     * 2019-02-21
     * 新版下发方法，同时生成.DB文件
     */
    @SuppressWarnings("unchecked")
    public String cardBlackListDownloadCenter() throws Exception {

        log.info("开始查询营改增全量用户卡黑名单数据");

        List<tbl_ParamInfo> BlackList = new ArrayList<tbl_ParamInfo>();
        int cardNum = cardBlackDownloadDao.allCardBlackListCount();
        //int cardNum = (Integer) sqlMapClient.queryForObject("user_ORACLE.allCardBlackListCount");
        int obuNum = cardBlackDownloadDao.allOBUBlackListCount();
       // int obuNum = (Integer) sqlMapClient.queryForObject("user_ORACLE.allOBUBlackListCount");

        boolean flag =true;

        /**根据下发记录获取黑名单数据**/

        if(cardNum>0&&obuNum>0){

            log.info("开始查询黑名单数据");

            BlackList = null;
            BlackList = cardBlackDownloadDao.getAllBlackList();
           // BlackList =sqlMapClient.queryForList("user_ORACLE.getAllBlackList");//根据版本号和表名获取黑名单数据

            log.info("本次共有数据："+BlackList.size());

            int recordCount = BlackList.size();

            if(recordCount>0){
                //生成.DB文件
                flag = createFile(BlackList);
                if(flag){
                    tbl_ParaVerInfoDic paraver = new tbl_ParaVerInfoDic();
                    paraver.setParaID(29);
                    paraver.setVer(Long.valueOf(this.fileId));
                    paraver.setRecordCount(recordCount);
                    paraver.setCreateTime(new Date());
                    paraver.setStartTime(new Date());
                    paraver.setSpare1(0);
                    paraver.setSpare3(filePath+this.fileId+".db");
                    cardBlackDownloadDao.insertParamVerinfo(paraver);
                    //sqlMapClient2.insert("check_ORACLE.insertParamVerinfo",paraver);

                    log.info("用户卡更新下发记录时间:"+DateUtil.formatDateTime(new Date()));

                }
            }else{
                log.info("当前无黑名单记录");
            }




        }else{



        }

        return "用户卡黑名单全量下载至省中心结束...";
    }

    /**
     * 查询是否存在当天已处理的全量版本记录信息
     * @return versionInfo全量版本记录
     * @throws Exception
     */
    private int getFullVersionInfo() throws Exception {
        int versionInfo = 0;
        HashMap<String, Object> full_versionMap = new HashMap<String, Object>();
        String full_version = DateUtil.formatDateTime1(new Date()).substring(0, 8);//全量用户卡黑名单版本
        full_versionMap.put("version", full_version);
        full_versionMap.put("fileType",1);//用户卡黑名单
        full_versionMap.put("type", 0);//全量
        full_versionMap.put("result", 1);//已处理

        /**查询当天已处理的全量版本记录信息***/
        versionInfo = cardBlackDownloadDao.getVersionInfo(full_versionMap);
       // versionInfo =  (Integer) sqlMapClient.queryForObject("download_ORACLE.getVersionInfo", full_versionMap);
        return versionInfo;
    }


    /**
     * 封装中心黑名单对象
     * **/
    private TBL_CardStListPara genCardStPara(Tbl_cardBlackList blackCard){

        TBL_CardStListPara cardStListPara = new TBL_CardStListPara();

        try{

            String recordCardId = blackCard.getCardId().substring(4,20);//卡号

            cardStListPara.setCardId(recordCardId);

            if(recordCardId!=null && !recordCardId.equals("null")){

                cardStListPara.setCardType(0);//表示储值卡和记账卡类型

                /***
                 * 交通部定义黑名单类型
                 * 1-挂失卡
                 * 2-无卡挂起
                 * 3-无卡注销
                 * 4-账户透支
                 * 5-合作机构黑名单
                 * 6-车型不符
                 * **/
                /***
                 * 青海省定义黑名单类型
                 * 1-挂失卡
                 * 2-禁用
                 * 3-注销
                 * 4-透支
                 * 5-黑名单卡
                 * **/

                if(blackCard.getType()==1){//挂失

                    cardStListPara.setStatus(1);//挂失

                }else if(blackCard.getType()==5){//合作机构黑名单

                    cardStListPara.setStatus(5);//黑名单卡

                }else if(blackCard.getType()==3){//无卡注销

                    cardStListPara.setStatus(3);//注销

                }else if(blackCard.getType()==4){//透支

                    cardStListPara.setStatus(4);//透支

                }else if(blackCard.getType()==2){//挂起

                    cardStListPara.setStatus(2);//禁用

                }
                else{

                    cardStListPara.setStatus(6);//禁用

                }

            }else{
                log.info("无对应用户卡号");
            }

            cardStListPara.setNetNo(Integer.valueOf(blackCard.getCardId().substring(0,4)));//网络编号

            cardStListPara.setIssuerId(blackCard.getIssuerId());//发行方id

            cardStListPara.setListType(0); //名单类型0:全量1:增量

            cardStListPara.setVersion(blackCard.getVersion());  //版本号

            cardStListPara.setStartTime(blackCard.getCreationTime());//生效时间

            cardStListPara.setUpdateTime(blackCard.getUploadtime()); //更新时间

            cardStListPara.setLicense("0");    //营改增后无车牌号

            cardStListPara.setSpare2(DateUtil.formatDateTime(new Date()));

        }catch(Exception e){

            log.error("用户黑名单由中间表格式转为本系统格式时异常"+e.getMessage());

            e.printStackTrace();

        }

        return cardStListPara;

    }
    /**
     * 解析存储全量历史黑名单数据
     * @author WAGNYP
     * @throws Exception
     */

    @Override
    public String cardBlackHisDownload() throws Exception {
        //查询版本信息表，从中获取待处理的全量版本信息
        //根据信息中的文件包名，从下载文件夹中获取相应的压缩包
        //解析压缩包并将数据存入黑名单历史表中
        //更新版本信息为已进行黑名单存储状态
        tbl_VersonInfo versionInfo = null;
        TBL_CardStListPara cardBlackhis;
        versionInfo = cardBlackDownloadDao.selectVersionInfo();
       // versionInfo = (tbl_VersonInfo) sqlMapClient.queryForObject("download_ORACLE.selectVersionInfo");
        List<Tbl_CardBlackListDown> list =null;
        if(versionInfo!=null&&versionInfo.getPackageName()!=""){
            String fileName  = versionInfo.getPackageName();
            String filepath = "D:\\unzip\\success\\";//处理成功的全量黑名单存储在此路径下
            String downloadPath = "D:\\unzip\\his\\";
            File file = new File(filepath+fileName);
            //在下载路径下解压原始压缩包
            ZIPUtil.unzip(file.getAbsolutePath(),downloadPath , true);
            //获取原始压缩包下的二级压缩包文件
            String filePath = downloadPath+file.getName().substring(0, file.getName().lastIndexOf("."));
            List<File> unzipfiles = ZIPUtil.getFiles(filePath);
            //获取原始压缩包下的二级压缩包(二级压缩包只有一个)
            if(unzipfiles!=null&&unzipfiles.size()>0){

                File zipfile =  unzipfiles.get(0);//F:\BASIC_CARDBLACKLISTDOWN_RES_65_20171023165050007\20171023.zip

//				String workFilePath = "D:\\unzip\\work";//保存二级压缩包文件至该目录下
//
//				String successFilePath ="D:\\unzip\\success";//将处理成功的原始压缩包保存至该目录下
//
//				String errorFilePath ="D:\\unzip\\error";//将处理失败的原始压缩包保存至该目录下

                String secondParentfileName = zipfile.getName();//二级压缩包文件名带后缀.zip

                String secondParentpath = secondParentfileName.substring(0, secondParentfileName.lastIndexOf("."));//二级压缩包解压后的文件夹名
                //将二级压缩包转移至workFilePath下
                ZIPUtil.moveFile(zipfile.getAbsolutePath(),downloadPath+"\\"+secondParentfileName );//F:\\unzip\\work\\20171023.zip
                //将二级压缩包在workFilePath解压缩
                ZIPUtil.unzip(downloadPath+"\\"+secondParentfileName, downloadPath, true);//F:\\unzip\\work\\20171023
                //获取二级压缩包解压后的各省份压缩包文件(三级压缩包)
                List<File> secondfiles = ZIPUtil.getFiles(downloadPath+"\\"+secondParentpath);//F:\\unzip\\work\\20171023
                //逐个对各省份的压缩包进行解压，并对解压后的JSON文件进行解析

                for(int i=0;i<secondfiles.size();i++){
                    //获取某个三级压缩包的文件名
                    String thirdParentfileName = secondfiles.get(i).getName();

                    String thirdParentpath = thirdParentfileName.substring(0, thirdParentfileName.lastIndexOf("."));//

                    System.out.println(secondfiles.get(i).getAbsolutePath());//F:\\unzip\\work\\20171023\\20171023_12.zip
                    //解压三级压缩包
                    ZIPUtil.unzip(downloadPath+"\\"+secondParentpath+"\\"+thirdParentfileName, downloadPath+"\\"+secondParentpath, true);
                    //获取三级压缩包解压后的JSON文件
                    List<File>	thirdfiles = ZIPUtil.getFiles(downloadPath+"\\"+secondParentpath+"\\"+thirdParentpath);//F:\\unzip\\work\\20171023\\20171023\\20171023_12
                    //循环处理每个JSON文件
                    for(int m=0;m<thirdfiles.size();m++){
                        //获取JSON文件中的字符串
                        String jsonStr = ZIPUtil.getjsonStr(thirdfiles.get(m).getAbsolutePath());
                        //字符串转化为JSON数组对象
                        JSONArray jsonArray = JSONArray.fromObject(jsonStr);
                        //循环处理每个JSON对象
                        Date a=new Date();
                        log.info("开始处理一个Json文件，当前时间："+DateUtil.formatDateTime(a));
                        if(jsonArray!=null&&jsonArray.size()>0){

                            Tbl_CardBlackListDown CardBlackListDownInfo = new Tbl_CardBlackListDown();
                            list =  new ArrayList<Tbl_CardBlackListDown>();
                            //将json数据转化为list
                            for(int  n = 0; n <jsonArray.size(); n++){

                                JSONObject jsonObject = jsonArray.getJSONObject(n);
                                CardBlackListDownInfo = getCardBlackListDownInfo(jsonObject);
                                CardBlackListDownInfo.setVersion(versionInfo.getVersion());
                                list.add(CardBlackListDownInfo);
                            }


                            /********生成历史数据并插入历史表
                             * 1.封装生成历史数据
                             * 2.插入历史数据
                             * *********/
                            try{

                               // sqlMapClient2.startTransaction();

                               // sqlMapClient2.startBatch();

                                for(int  n = 0; n <list.size(); n++){

                                    CardBlackListDownInfo = list.get(n);

                                    CardBlackListDownInfo.setVersion(versionInfo.getVersion());

                                    cardBlackhis  = getCardBlackHis(CardBlackListDownInfo,0);//0-表示全量名单1-增量名单

                                    cardBlackhis.setSpare2(String.valueOf(versionInfo.getSpare2()));
                                    cardBlackDownloadDao.insertHisCardBlackDownInfo(cardBlackhis);
                                    //sqlMapClient2.insert("download_ORACLE.insertHisCardBlackDownInfo",cardBlackhis);
                                }

                               // sqlMapClient2.executeBatch();

                               // sqlMapClient2.commitTransaction();

                            }catch(Exception e){
                                log.info("用户卡全量黑名单插入历史表异常:"+e.getMessage());
                               // sqlMapClient2.getCurrentConnection().rollback();
                            }finally{
                               // sqlMapClient2.endTransaction();
                            }
                            /*****************************************/
                        }
                        Date b = new Date();
                        log.info("处理一个Json文件完成，耗时："+(b.getTime()-a.getTime())/1000+"秒");
                    }

                }
                cardBlackDownloadDao.updateVersinInfo(versionInfo.getRecordId());
               //sqlMapClient.update("download_ORACLE.updateVersinInfo",versionInfo.getRecordId());

                //删除workFilePath下的二级压缩包和解压缩文件
                ZIPUtil.delFile(downloadPath+"\\"+secondParentfileName);//二级压缩包

                ZIPUtil.deleteDir(new File(downloadPath+"\\"+secondParentpath));//删除解压缩文件及文件下的所有文件

                ZIPUtil.deleteDir(new File(filePath));//删除解压缩文件及文件下的所有文件

            }

            return "处理历史黑名单文件成功,版本号为"+versionInfo.getVersion();
        }else{
            return "无需要处理的历史黑名单文件";
        }

    }
    /**
     *生成.DB文件
     */
    public boolean createFile(List<tbl_ParamInfo> BlackList) throws Exception{

        boolean flag = true;

        int gbCardCount = BlackList.size();

        filePath = OperType.filePath;

        //判断文件夹是否存在
        File dir = new File(filePath);

        if (dir.exists()) {
            if (dir.isDirectory()) {
                System.out.println("dir exists");
            } else {
                System.out.println("the same name file exists, can not create dir");
            }
        } else {
            System.out.println("dir not exists, create it ...");
            dir.mkdir();
        }

        fileId = getFileID();

        tbl_ParamInfo gbCardBlack = null;

        try{

            file = new File(filePath + fileId + ".db");

            log.info("新建文件URL："+file);

            if (!file.exists()) {

                file.createNewFile();

            } else {

                file.delete();

                file.createNewFile();

            }
        }catch(IOException e){
            //文件新建删除操作失败
            log.error("文件新建删除操作失败"+e.getMessage());

            throw new Exception("文件新建删除操作失败"+e.getMessage());

        }

        Class.forName("org.sqlite.JDBC");
        Connection conn =
                DriverManager.getConnection("jdbc:sqlite:"+filePath+ fileId + ".db");
        Statement stat = conn.createStatement();
        stat.executeUpdate("DROP TABLE IF EXISTS 'tbl_ParamInfo';");
        stat.executeUpdate("CREATE TABLE tbl_ParamInfo ( [CardNet] INTEGER ,[CardID] TEXT , [BListType] INTEGER);");
        stat.executeUpdate("CREATE INDEX 'idx_tbl_ParamInfo1' ON 'tbl_ParamInfo' ('CardNet' ASC, 'CardID' ASC);");

        /***********************生成黑名单表****************/
        PreparedStatement prep = conn.prepareStatement("insert into tbl_ParamInfo values (?, ?,?);");

        for(int i=BlackList.size()-1;i>-1;i--){
            gbCardBlack = BlackList.get(i);

            String cardIDT=gbCardBlack.getCardID();
            if(cardIDT == null){
                continue;
            }
            prep.setInt(1, gbCardBlack.getCardNet());
            prep.setString(2, gbCardBlack.getCardID().trim());
            prep.setInt(3, gbCardBlack.getBListType());

            prep.addBatch();

            BlackList.remove(i);
        }

        BlackList = null;
        conn.setAutoCommit(false);
        prep.executeBatch();
        conn.commit();
        /***********************生成版本信息****************/
        stat.executeUpdate("DROP TABLE IF EXISTS 'tbl_VersionInfo';");
        stat.executeUpdate("CREATE TABLE tbl_VersionInfo( [FileID] INTEGER PRIMARY KEY, [tmVersion] TEXT, [RecordCount] INTEGER, [sysUseTime] TEXT);");
        String sql ="insert into tbl_VersionInfo values(29,'" +fileId+"',"+gbCardCount+",'" + DateUtil.getFullDateString2()+"');";
        stat.executeUpdate(sql);

        conn.commit();

        conn.close();

        return flag;
    }

    /**
     * 获取文件版本
     * @return
     * @throws SQLException
     */
    @SuppressWarnings("unchecked")
    public String getFileID() throws SQLException {
        ArrayList<tbl_ParaVerInfoDic> list = cardBlackDownloadDao.selectParamVerinfo(DateUtil.getFullDateString3());
        //ArrayList<tbl_ParaVerInfoDic> list  = (ArrayList<tbl_ParaVerInfoDic>) sqlMapClient2.queryForList("check_ORACLE.selectParamVerinfo",DateUtil.getFullDateString3());
        if(list==null||list.size()==0){
            fileId = DateUtil.getFullDateString3()+"01";
        }else{
            fileId = DateUtil.getFullDateString3()+ NumberUtil.compStr0(String.valueOf(list.size()+1), 2);
        }

        return fileId;
    }
}
